<?php

class ReportController extends Controller
{

	/**
     * 过滤器
	 * @return 返回值是一个存放过滤器的数组
	 */
	public function filters()
	{
		return array(
            'accessAuth',//自定义授权方式
			//'accessControl', //默认授权方式
			'postOnly + delete',//删除操作只支持post请求
		);
	}
    /**
     * 自定义权限验证规则
     * @param type $filterChain
     * @throws CHttpException
     */
    public function filterAccessAuth($filterChain) {  
        if(Yii::app()->user->getIsGuest()) {  
            echo '<script>top.location.href="'.Yii::app()->user->returnUrl.'";</script>';
            exit;
        } else if(!Yii::app()->user->getIsGuest() && !in_array($this->getAction()->getId(), $this->authlessActions())){
            throw new CHttpException(403,'无权访问此页面');
        }
        $filterChain->run();  
    }  
    /**
     * 页面白名单
     * @return type
     */
    public function authlessActions() {  
        return array(
            'admin','property','age','ageReport','data','sale','saleSearch','exportProperty','exportKey','dataExcel'
		); 
    }
    
    /**
     * 儿童年龄分析
     */
    public function actionAge(){
                $model=new Party('search');
		$model->unsetAttributes();  //清空并初始化
                
                $province="";
                $city="";
                $area="";
                $export=FALSE;
                if(isset($_GET['type'])){
                    $export=TRUE;
                }                
                if(isset($_GET['Party'])){
                    $province=$_GET['Party']['province'];
                    $city=$_GET['Party']['city'];
                    $area=$_GET['Party']['area'];
                    $model->city=$_GET['Party']['city'];
                    $model->area=$_GET['Party']['area'];
                    $model->province=$_GET['Party']['province'];
                }
                
                //查询符合条件的地区
                $sql="SELECT PROVINCE,CITY,AREA FROM party WHERE PROVINCE LIKE '%".$province."%' AND CITY LIKE '%".$city."%' AND AREA LIKE '%".$area."%' GROUP BY PROVINCE,CITY,AREA ORDER BY PROVINCE";
                $connection = Yii::app()->db;
                //print_r($sql);
                $command_1 = $connection->createCommand($sql);
                $result_1 = $command_1->queryAll();
                
                //total系列为表格最后一行统计值
                $total1=0;
                $total2=0;
                $total3=0;
                $total4=0;
                $total5=0;
                
                $res=array();
                for($temp=0;$temp < count($result_1);$temp++){
                        $p=$result_1[$temp]['PROVINCE'];
                        $c=$result_1[$temp]['CITY'];
                        $a=$result_1[$temp]['AREA'];
                                                
                        $res["$temp"]["1"]=$p;
                        $res["$temp"]["2"]=$c;
                        $res["$temp"]["3"]=$a;
                        
                        $sql="SELECT * FROM (
                            (SELECT count(*) as AGE1 FROM party as p RIGHT JOIN  children as c ON p.id=c.party_id WHERE p.province = '".$p."' AND p.CITY = '".$c."' AND p.AREA = '".$a."' AND c.AGE <3) AS AGE1,
                            (SELECT count(*) as AGE2 FROM party as p RIGHT JOIN  children as c ON p.id=c.party_id WHERE p.province = '".$p."' AND p.CITY = '".$c."' AND p.AREA = '".$a."' AND c.AGE >=3 AND c.AGE<6 ) AS AGE2,
                            (SELECT count(*) as AGE3 FROM party as p RIGHT JOIN  children as c ON p.id=c.party_id WHERE p.province = '".$p."' AND p.CITY = '".$c."' AND p.AREA = '".$a."' AND c.AGE >=6 AND c.AGE<12 ) AS AGE3,
                            (SELECT count(*) as AGE4 FROM party as p RIGHT JOIN  children as c ON p.id=c.party_id WHERE p.province = '".$p."' AND p.CITY = '".$c."' AND p.AREA = '".$a."' AND c.AGE >=12 AND c.AGE<16 ) AS AGE4,
                            (SELECT count(*) as AGE5 FROM party as p RIGHT JOIN  children as c ON p.id=c.party_id WHERE p.PROVINCE = '".$p."' AND p.CITY = '".$c."' AND p.AREA = '".$a."' AND c.AGE >=16 AND c.AGE<20) AS AGE5
                         )";
                        
                        $command_2 = $connection->createCommand($sql);
                        $result_2 = $command_2->queryAll();
                        $res["$temp"]["4"]=$result_2[0]['AGE1'];
                        $res["$temp"]["5"]=$result_2[0]['AGE2'];
                        $res["$temp"]["6"]=$result_2[0]['AGE3'];
                        $res["$temp"]["7"]=$result_2[0]['AGE4'];
                        $res["$temp"]["8"]=$result_2[0]['AGE5'];
                        $total1+=$result_2[0]['AGE1'];
                        $total2+=$result_2[0]['AGE2'];
                        $total3+=$result_2[0]['AGE3'];
                        $total4+=$result_2[0]['AGE4'];
                        $total5+=$result_2[0]['AGE5'];
                }
                $last="";
                foreach($res as $key => $value){
                        //去除重复省市表头显示
                        if($last==$res["$key"]["1"].$res["$key"]["2"]){
                            $res["$key"]["1"]="";
                            $res["$key"]["2"]="";
                        }else{
                            $last=$res["$key"]["1"].$res["$key"]["2"];
                        }
                    }

                $totals=array('$total1'=>$total1,'$total2'=>$total2,'$total3'=>$total3,'$total4'=>$total4,'$total5'=>$total5);
                if($export==TRUE){
                     include_once(dirname(dirname(__FILE__)).'/extensions/PHPExcel/PHPExcel/IOFactory.php');  
                    $phpexcel = new PHPExcel();
                    $phpexcel->setActiveSheetIndex(0);
                    $phpactiveSheet = $phpexcel->getActiveSheet();
                    $phpactiveSheet -> setTitle("来访儿童年龄分析");
                    $phpactiveSheet -> setCellValue("A1","省");
                    $phpactiveSheet -> setCellValue("B1","市");
                    $phpactiveSheet -> setCellValue("C1","区");
                    $phpactiveSheet -> setCellValue("D1","学龄前3岁以下");     
                    $phpactiveSheet -> setCellValue("E1","幼儿园3-6岁");
                    $phpactiveSheet -> setCellValue("F1","小学6-12岁");
                    $phpactiveSheet -> setCellValue("G1","初中12-16岁");
                    $phpactiveSheet -> setCellValue("H1","高中16-20岁");
                    $index=2;
                    foreach ($res as $value){
                        $phpactiveSheet -> setCellValue("A".$index,$value["1"]);
                        $phpactiveSheet -> setCellValue("B".$index,$value["2"]);
                        $phpactiveSheet -> setCellValue("C".$index,$value["3"]);
                        $phpactiveSheet -> setCellValue("D".$index,$value["4"]);     
                        $phpactiveSheet -> setCellValue("E".$index,$value["5"]);
                        $phpactiveSheet -> setCellValue("F".$index,$value["6"]);
                        $phpactiveSheet -> setCellValue("G".$index,$value["7"]);
                        $phpactiveSheet -> setCellValue("H".$index,$value["8"]);
                        $index++;
                    }
                    $phpactiveSheet -> setCellValue("A".$index,'合计');
                    $phpactiveSheet -> setCellValue("D".$index,$totals['$total1']);     
                    $phpactiveSheet -> setCellValue("E".$index,$totals['$total2']);
                    $phpactiveSheet -> setCellValue("F".$index,$totals['$total3']);
                    $phpactiveSheet -> setCellValue("G".$index,$totals['$total4']);
                    $phpactiveSheet -> setCellValue("H".$index,$totals['$total5']);     
                    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                    header('Content-Disposition: attachment;filename="children'.time().'.xls"');
                    header('Cache-Control: max-age=0');
                    $objWriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
                    $objWriter->save('php://output');
                    exit;
                }else{                
                    $this->render('age',array(
                            'model'=>$model,
                            'data'=>$model->search(),
                            'queryData_1'=>$res,
                            'queryData_2'=>$totals,
                    ));
                }
    }
    
    
    
    /**
     * 儿童年龄分析
     */
    public function actionAgeReport(){
                $model=new Party('search');
		$model->unsetAttributes();  //清空并初始化
                
                $province="";
                $city="";
                $area="";
                $export=FALSE;
                if(isset($_GET['type'])){
                    $export=TRUE;
                }                
                if(isset($_GET['Party'])){
                    $province=$_GET['Party']['province'];
                    $city=$_GET['Party']['city'];
                    $area=$_GET['Party']['area'];
                    $model->city=$_GET['Party']['city'];
                    $model->area=$_GET['Party']['area'];
                    $model->province=$_GET['Party']['province'];
                }
                
                //查询符合条件的地区
                $sql="SELECT PROVINCE,CITY,AREA FROM party WHERE PROVINCE LIKE '%".$province."%' AND CITY LIKE '%".$city."%' AND AREA LIKE '%".$area."%' GROUP BY PROVINCE,CITY,AREA ORDER BY PROVINCE";
                $connection = Yii::app()->db;
                //print_r($sql);
                $command_1 = $connection->createCommand($sql);
                $result_1 = $command_1->queryAll();
                
                //total系列为表格最后一行统计值
                $total1=0;
                $total2=0;
                $total3=0;
                $total4=0;
                $total5=0;
                
                $res=array();
                for($temp=0;$temp < count($result_1);$temp++){
                        $p=$result_1[$temp]['PROVINCE'];
                        $c=$result_1[$temp]['CITY'];
                        $a=$result_1[$temp]['AREA'];
                                                
                        $res["$temp"]["1"]=$p;
                        $res["$temp"]["2"]=$c;
                        $res["$temp"]["3"]=$a;
                        
                        $sql="SELECT * FROM (
                            (SELECT count(*) as AGE1 FROM party as p RIGHT JOIN  children as c ON p.id=c.party_id WHERE p.province = '".$p."' AND p.CITY = '".$c."' AND p.AREA = '".$a."' AND c.AGE <3) AS AGE1,
                            (SELECT count(*) as AGE2 FROM party as p RIGHT JOIN  children as c ON p.id=c.party_id WHERE p.province = '".$p."' AND p.CITY = '".$c."' AND p.AREA = '".$a."' AND c.AGE >=3 AND c.AGE<6 ) AS AGE2,
                            (SELECT count(*) as AGE3 FROM party as p RIGHT JOIN  children as c ON p.id=c.party_id WHERE p.province = '".$p."' AND p.CITY = '".$c."' AND p.AREA = '".$a."' AND c.AGE >=6 AND c.AGE<12 ) AS AGE3,
                            (SELECT count(*) as AGE4 FROM party as p RIGHT JOIN  children as c ON p.id=c.party_id WHERE p.province = '".$p."' AND p.CITY = '".$c."' AND p.AREA = '".$a."' AND c.AGE >=12 AND c.AGE<16 ) AS AGE4,
                            (SELECT count(*) as AGE5 FROM party as p RIGHT JOIN  children as c ON p.id=c.party_id WHERE p.PROVINCE = '".$p."' AND p.CITY = '".$c."' AND p.AREA = '".$a."' AND c.AGE >=16 AND c.AGE<20) AS AGE5
                         )";
                        
                        $command_2 = $connection->createCommand($sql);
                        $result_2 = $command_2->queryAll();
                        $res["$temp"]["4"]=$result_2[0]['AGE1'];
                        $res["$temp"]["5"]=$result_2[0]['AGE2'];
                        $res["$temp"]["6"]=$result_2[0]['AGE3'];
                        $res["$temp"]["7"]=$result_2[0]['AGE4'];
                        $res["$temp"]["8"]=$result_2[0]['AGE5'];
                        $total1+=$result_2[0]['AGE1'];
                        $total2+=$result_2[0]['AGE2'];
                        $total3+=$result_2[0]['AGE3'];
                        $total4+=$result_2[0]['AGE4'];
                        $total5+=$result_2[0]['AGE5'];
                }
                $last="";
                foreach($res as $key => $value){
                        //去除重复省市表头显示
                        if($last==$res["$key"]["1"].$res["$key"]["2"]){
                            $res["$key"]["1"]="";
                            $res["$key"]["2"]="";
                        }else{
                            $last=$res["$key"]["1"].$res["$key"]["2"];
                        }
                    }

                $totals=array('$total1'=>$total1,'$total2'=>$total2,'$total3'=>$total3,'$total4'=>$total4,'$total5'=>$total5);
                if($export==TRUE){
                     include_once(dirname(dirname(__FILE__)).'/extensions/PHPExcel/PHPExcel/IOFactory.php');  
                    $phpexcel = new PHPExcel();
                    $phpexcel->setActiveSheetIndex(0);
                    $phpactiveSheet = $phpexcel->getActiveSheet();
                    $phpactiveSheet -> setTitle("来访儿童年龄分析");
                    $phpactiveSheet -> setCellValue("A1","省");
                    $phpactiveSheet -> setCellValue("B1","市");
                    $phpactiveSheet -> setCellValue("C1","区");
                    $phpactiveSheet -> setCellValue("D1","学龄前3岁以下");     
                    $phpactiveSheet -> setCellValue("E1","幼儿园3-6岁");
                    $phpactiveSheet -> setCellValue("F1","小学6-12岁");
                    $phpactiveSheet -> setCellValue("G1","初中12-16岁");
                    $phpactiveSheet -> setCellValue("H1","高中16-20岁");
                    $index=2;
                    foreach ($res as $value){
                        $phpactiveSheet -> setCellValue("A".$index,$value["1"]);
                        $phpactiveSheet -> setCellValue("B".$index,$value["2"]);
                        $phpactiveSheet -> setCellValue("C".$index,$value["3"]);
                        $phpactiveSheet -> setCellValue("D".$index,$value["4"]);     
                        $phpactiveSheet -> setCellValue("E".$index,$value["5"]);
                        $phpactiveSheet -> setCellValue("F".$index,$value["6"]);
                        $phpactiveSheet -> setCellValue("G".$index,$value["7"]);
                        $phpactiveSheet -> setCellValue("H".$index,$value["8"]);
                        $index++;
                    }
                    $phpactiveSheet -> setCellValue("A".$index,'合计');
                    $phpactiveSheet -> setCellValue("D".$index,$totals['$total1']);     
                    $phpactiveSheet -> setCellValue("E".$index,$totals['$total2']);
                    $phpactiveSheet -> setCellValue("F".$index,$totals['$total3']);
                    $phpactiveSheet -> setCellValue("G".$index,$totals['$total4']);
                    $phpactiveSheet -> setCellValue("H".$index,$totals['$total5']);     
                    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                    header('Content-Disposition: attachment;filename="children'.time().'.xls"');
                    header('Cache-Control: max-age=0');
                    $objWriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
                    $objWriter->save('php://output');
                    exit;
                }else{                
                    $this->render('age',array(
                            'model'=>$model,
                            'data'=>$model->search(),
                            'queryData_1'=>$res,
                            'queryData_2'=>$totals,
                    ));
                }
    }
    
    /**
     * 资料分析
     */
    public function actionData(){
                $model=new Party('search');
		$model->unsetAttributes();  //清空并初始化
                
                $province="";
                $city="";
                $area="";
                if(isset($_GET['Party'])){
                    $province=$_GET['Party']['province'];
                    $city=$_GET['Party']['city'];
                    $area=$_GET['Party']['area'];
                    $model->city=$_GET['Party']['city'];
                    $model->area=$_GET['Party']['area'];
                    $model->province=$_GET['Party']['province'];
                }
                //查询符合条件的地区
                $sql="SELECT PROVINCE,CITY,AREA FROM party WHERE PROVINCE LIKE '%".$province."%' AND CITY LIKE '%".$city."%' AND AREA LIKE '%".$area."%' GROUP BY PROVINCE,CITY,AREA ORDER BY PROVINCE";
                $connection = Yii::app()->db;
                //print_r($sql);
                $command_1 = $connection->createCommand($sql);
                $result_1 = $command_1->queryAll();
                //print_r($result_1);
                $res=array();
                //total系列为表格最后一行统计值
                $total=0;
                $total2=0;
                $total3=0;
                $total4=0;
                $total5=0;
                $total6=0;
                $total7=0;
                $total8=0;
                
                //标志位 记录是否是同一个地区的数据
                $flag_province=0;
                $flag_city="";
                $total_area=0;
                $temp=0;
                for($temp;$temp < count($result_1);$temp++){
                        $p=$result_1[$temp]['PROVINCE'];
                        $c=$result_1[$temp]['CITY'];
                        $a=$result_1[$temp]['AREA'];
                                                
                        $res["$temp"]["1"]=$p;
                        $res["$temp"]["2"]=$c;
                        $res["$temp"]["3"]=$a;
                                                
                        $sql="SELECT * FROM (
                            (SELECT COUNT(*) AS TOTAL FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."') AS TOTAL,
                            (SELECT COUNT(*) AS MAN FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND SEX ='男') AS MAN,
                            (SELECT COUNT(*) AS WOMAN FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND SEX ='女') AS WOMAN,
                            (SELECT COUNT(*) AS AGE1 FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND AGE <20) AS AGE1,
                            (SELECT COUNT(*) AS AGE2 FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND AGE >=20 AND AGE<30 ) AS AGE2,
                            (SELECT COUNT(*) AS AGE3 FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND AGE >=30 AND AGE<40 ) AS AGE3,
                            (SELECT COUNT(*) AS AGE4 FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND AGE >=40 AND AGE<50 ) AS AGE4,
                            (SELECT COUNT(*) AS AGE5 FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND AGE >=50) AS AGE5
                         )";
                        
                        $command_2 = $connection->createCommand($sql);
                        $result_2 = $command_2->queryAll();
                        $res["$temp"]["4"]=$result_2[0]['TOTAL'];
                        $res["$temp"]["5"]=round(($result_2[0]['MAN']/$result_2[0]['TOTAL'])*100,0)."%";
                        $res["$temp"]["6"]=round(($result_2[0]['WOMAN']/$result_2[0]['TOTAL'])*100,0)."%";
                        $res["$temp"]["7"]=$result_2[0]['AGE1'];
                        $res["$temp"]["8"]=$result_2[0]['AGE2'];
                        $res["$temp"]["9"]=$result_2[0]['AGE3'];
                        $res["$temp"]["10"]=$result_2[0]['AGE4'];
                        $res["$temp"]["11"]=$result_2[0]['AGE5'];
                        $res["$temp"]["12"]=0;
                        $res["$temp"]["13"]=0;
                        $total+=$result_2[0]['TOTAL'];
                        $total2+=$result_2[0]['MAN'];
                        $total3+=$result_2[0]['WOMAN'];
                        $total4+=$result_2[0]['AGE1'];
                        $total5+=$result_2[0]['AGE2'];
                        $total6+=$result_2[0]['AGE3'];
                        $total7+=$result_2[0]['AGE4'];
                        $total8+=$result_2[0]['AGE5'];
                        
                        //计算区域占比
                        if($flag_city==""){
                            $flag_province=$p;
                            $flag_city=$c;
                            $total_area+=$result_2[0]['TOTAL'];
                        }else{
                            if($flag_province==$p && $flag_city==$c){
                                $total_area+=$result_2[0]['TOTAL'];
                            }else{
                                $index=$temp-1;
                                while($index>=0&&$res["$index"][1]==$flag_province && $res["$index"][2]==$flag_city){
                                    $val=number_format($res["$index"]["4"]/$total_area*100,2);
                                    $res["$index"]["13"]=$val ."%";
                                    $index--;
                                }
                                $flag_province=$p;
                                $flag_city=$c;
                                $total_area=$result_2[0]['TOTAL'];
                            }
                        }
                }
                $totals=array('$total1'=>0,'$total2'=>0 ."%",'$total3'=>0 ."%",'$total4'=>0,'$total5'=>0,'$total6'=>0,'$total7'=>0,'$total8'=>0);
                if($total>0){
                    //计算最后一个省市数据的区域占比
                    $index=$temp-1;
                    while($index>=0&&$res["$index"][1]==$flag_province && $res["$index"][2]==$flag_city){
                        $res["$index"]["13"]=$res["$index"]["4"]/$total_area*100 ."%";
                        $index--;
                    }
                    
                    $last="";
                    foreach($res as $key => $value){
                        //计算整体占比
                        $res["$key"]["12"]=round(($res["$key"]["4"]/$total)*100,0)."%";
                        //去除重复省市表头显示
                        if($last==$res["$key"]["1"].$res["$key"]["2"]){
                            $res["$key"]["1"]="";
                            $res["$key"]["2"]="";
                        }else{
                            $last=$res["$key"]["1"].$res["$key"]["2"];
                        }
                    }
                                
                    $totals=array('$total1'=>$total,'$total2'=>round($total2/$total*100,0)."%",'$total3'=>round($total3/$total*100,0)."%",'$total4'=>$total4,'$total5'=>$total5,'$total6'=>$total6,'$total7'=>$total7,'$total8'=>$total8);
                }
                    $this->render('data',array(
                            'model'=>$model,
                            'data'=>$model->search(),
                            'queryData_1'=>$res,
                            'queryData_2'=>$totals,
                    ));                    
                
    }
    
    /**
     * 人群属性分析
     */
    public function actionProperty(){
                $model=new Party('search');
		$model->unsetAttributes();  //清空并初始化
                $date=new DateTime();
                $date->modify('this week');
                $start=$date->format('Y-m-d');
                $date->modify('this week +6 days');
                $end=$date->format('Y-m-d');
                if(isset($_GET['Party'])){
                    $end=$_GET['Party']['update_time'];
                    $start=$_GET['Party']['add_time'];
                    $model->update_time=$end;
                    $model->add_time=$start;    
                }else{
                    $model->update_time=$end;
                    $model->add_time=$start; 
                }
                if($start==""){
                    $date=new DateTime();
                    $date->modify('this week');
                    $start=$date->format('Y-m-d');
                }
                if($end==""){
                    $date=new DateTime();
                    $date->modify('this week +6 days');
                    $end=$date->format('Y-m-d');
                }
                
                $connection = Yii::app()->db;
                $res=array();
                
                $sql="SELECT date_format(ADD_TIME, '%Y-%m-%d') as d  FROM party where date_format(ADD_TIME, '%Y-%m-%d')>='".$start."' and date_format(ADD_TIME, '%Y-%m-%d')<='".$end."' GROUP BY d";
                $command = $connection->createCommand($sql);
                $result = $command->queryAll();
 
                $crowd=Crowd::model()->findAll();
                $res=array();
                $i=0;
                foreach ($result as $v){
                    $res[$i][0]=$v['d'];
                    $z=1;
                    foreach ($crowd as $c){
                        $count=Party::model()->count("date_format(ADD_TIME, '%Y-%m-%d')='".$v['d']."' and crowd_id=".$c->id);
                        $res[$i][$z]=$count;
                        $res[$i][$z]=$count;
                        $z++;
                    }
                    $i++;
                }
                
		$this->render('property',array(
			'model'=>$model,
			'data'=>$model->search(),
                        'queryData_1'=>$res,
                        'crowd'=>$crowd,
		));
    }
    
    /**
     * 人群属性分析
     */
    public function actionExportProperty(){
                $model=new Party('search');
		$model->unsetAttributes();  //清空并初始化
                $date=new DateTime();
                $date->modify('this week');
                $start=$date->format('Y-m-d');
                $date->modify('this week +6 days');
                $end=$date->format('Y-m-d');
                if(isset($_GET['Party'])){
                    $end=$_GET['Party']['update_time'];
                    $start=$_GET['Party']['add_time'];
                    $model->update_time=$end;
                    $model->add_time=$start;    
                }else{
                    $model->update_time=$end;
                    $model->add_time=$start; 
                }
                if($start==""){
                    $date=new DateTime();
                    $date->modify('this week');
                    $start=$date->format('Y-m-d');
                }
                if($end==""){
                    $date=new DateTime();
                    $date->modify('this week +6 days');
                    $end=$date->format('Y-m-d');
                }
                
                $connection = Yii::app()->db;
                $res=array();
                
                $sql="SELECT date_format(ADD_TIME, '%Y-%m-%d') as d  FROM party where date_format(ADD_TIME, '%Y-%m-%d')>='".$start."' and date_format(ADD_TIME, '%Y-%m-%d')<='".$end."' GROUP BY d";
                $command = $connection->createCommand($sql);
                $result = $command->queryAll();
 
                $crowd=Crowd::model()->findAll();
                $res=array();
                $i=0;
                foreach ($result as $v){
                    $res[$i][0]=$v['d'];
                    $z=1;
                    foreach ($crowd as $c){
                        $count=Party::model()->count("date_format(ADD_TIME, '%Y-%m-%d')='".$v['d']."' and crowd_id=".$c->id);
                        $res[$i][$z]=$count;
                        $res[$i][$z]=$count;
                        $z++;
                    }
                    $i++;
                }
                
                
                include_once(dirname(dirname(__FILE__)).'/extensions/PHPExcel/PHPExcel/IOFactory.php');  
		$phpexcel = new PHPExcel();
                $phpexcel->setActiveSheetIndex(0);
                $phpactiveSheet = $phpexcel->getActiveSheet();
                $phpactiveSheet -> setTitle("人群属性分析");
                $phpactiveSheet -> setCellValue("A1","时间");
                
                $cell=array('A','B','C','D','E','F','G','H','I');
                $i=1;
                foreach ($crowd as $c){
                    $phpactiveSheet -> setCellValue($cell[$i].'1',$c->title);
                    $i++;
                }
                $index=2;
                foreach ($res as $key => $value) {
                    $f=0;
                    foreach ($value as $key => $z) {
                        $phpactiveSheet -> setCellValue($cell[$f].$index,$z);
                        $f++;
                    }
                    $index++;
                }
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="renqunshuxing'.time().'.xls"');
                header('Cache-Control: max-age=0');
                $objWriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
                $objWriter->save('php://output');
                exit;
    }

	/**
	 * 列表页 关键指标分析
	 */
	public function actionAdmin(){
		$model=new Party('search');
		$model->unsetAttributes();  //清空并初始化
                
               
                $date=new DateTime();
                $date->modify('this week');
                $start=$date->format('Y-m-d');
                $date->modify('this week +6 days');
                $end=$date->format('Y-m-d');
                if(isset($_GET['Party'])){
                    $end=$_GET['Party']['update_time'];
                    $start=$_GET['Party']['add_time'];
                    $model->update_time=$end;
                    $model->add_time=$start;    
                }else{
                    $model->update_time=$end;
                    $model->add_time=$start; 
                }
                if($start==""){
                    $date=new DateTime();
                    $date->modify('this week');
                    $start=$date->format('Y-m-d');
                }
                if($end==""){
                    $date=new DateTime();
                    $date->modify('this week +6 days');
                    $end=$date->format('Y-m-d');
                }

                $connection = Yii::app()->db;
                //$sql_1="SELECT date_format(ADD_TIME, '%Y-%m-%d') AS D,COUNT(*) as S1 FROM party WHERE ADD_TIME BETWEEN '".$start."' AND '".$end ."' GROUP BY date_format(ADD_TIME, '%Y-%m-%d')";
                  $sql_1="SELECT date_format(date, '%Y-%m-%d') AS D,click_no AS CLICK FROM click_rate WHERE date BETWEEN '".$start."' AND '".$end ."'";
                //print_r($sql);
                $command_1 = $connection->createCommand($sql_1);
                $result_1 = $command_1->queryAll();
                for($temp = 0;$temp < count($result_1);$temp++){
                    $d=$result_1[$temp]['D'];
                    //$s1=$result_1[$temp]['S1'];
                    $sql_2="SELECT COUNT(*) AS S2 FROM party WHERE IS_FROM = 0 AND date_format(ADD_TIME, '%Y-%m-%d') = '".$d."'";
                    $command_2 = $connection->createCommand($sql_2);
                    $result_2 = $command_2->queryAll();
                    $s2=$result_2[0]['S2'];
                    $result_1[$temp]['S2']=$s2;
                    
                    $sql_3="SELECT COUNT(*) AS S1 FROM party WHERE  date_format(ADD_TIME, '%Y-%m-%d') = '".$d."'";
                    $command_3 = $connection->createCommand($sql_3);
                    $result_3 = $command_3->queryAll();
                    $s1=$result_3[0]['S1'];
                    $result_1[$temp]['S1']=$s1;
 
                }
		$this->render('admin',array(
			'model'=>$model,
			'data'=>$model->search(),
                        'queryData_1'=>$result_1,
		));
	}
        
        /**
	 * 列表页 关键指标分析
	 */
	public function actionExportKey(){
		$model=new Party('search');
		$model->unsetAttributes();  //清空并初始化
                
               
                $date=new DateTime();
                $date->modify('this week');
                $start=$date->format('Y-m-d');
                $date->modify('this week +6 days');
                $end=$date->format('Y-m-d');
                if(isset($_GET['Party'])){
                    $end=$_GET['Party']['update_time'];
                    $start=$_GET['Party']['add_time'];
                    $model->update_time=$end;
                    $model->add_time=$start;    
                }else{
                    $model->update_time=$end;
                    $model->add_time=$start; 
                }
                if($start==""){
                    $date=new DateTime();
                    $date->modify('this week');
                    $start=$date->format('Y-m-d');
                }
                if($end==""){
                    $date=new DateTime();
                    $date->modify('this week +6 days');
                    $end=$date->format('Y-m-d');
                }

                $connection = Yii::app()->db;
                $sql_1="SELECT date_format(ADD_TIME, '%Y-%m-%d') AS D,COUNT(*) as S1 FROM party WHERE ADD_TIME BETWEEN '".$start."' AND '".$end ."' GROUP BY date_format(ADD_TIME, '%Y-%m-%d')";
                //print_r($sql);
                $command_1 = $connection->createCommand($sql_1);
                $result_1 = $command_1->queryAll();
                for($temp = 0;$temp < count($result_1);$temp++){
                    $d=$result_1[$temp]['D'];
                    $s1=$result_1[$temp]['S1'];
                    $sql_2="SELECT COUNT(*) AS S2 FROM party WHERE IS_FROM = 0 AND date_format(ADD_TIME, '%Y-%m-%d') = '".$d."'";
                    $command_2 = $connection->createCommand($sql_2);
                    $result_2 = $command_2->queryAll();
                    $s2=$result_2[0]['S2'];
                    $result_1[$temp]['S2']=$s2;
                    
                    $sql_3="SELECT click_no AS CLICK FROM click_rate WHERE date_format(date, '%Y-%m-%d') = '".$d."'";
                    $command_3 = $connection->createCommand($sql_3);
                    $result_3 = $command_3->queryAll();
                    if(count($result_3)>0){
                        $CLICK=$result_3[0]['CLICK'];
                        $result_1[$temp]['CLICK']=$CLICK;
                    }else{
                        $result_1[$temp]['CLICK']='暂无';
                    }
                }
		 include_once(dirname(dirname(__FILE__)).'/extensions/PHPExcel/PHPExcel/IOFactory.php');  
                $phpexcel = new PHPExcel();
                $phpexcel->setActiveSheetIndex(0);
                $phpactiveSheet = $phpexcel->getActiveSheet();
                $phpactiveSheet -> setTitle("关键指标分析");
                $phpactiveSheet -> setCellValue("A1","时间");
                $phpactiveSheet -> setCellValue("B1","点击量");
                $phpactiveSheet -> setCellValue("C1","报名量");
                $phpactiveSheet -> setCellValue("D1","报名未来量");     
                $index=2;
                foreach ($result_1 as $value){
                    $phpactiveSheet -> setCellValue("A".$index,$value["D"]);
                    $phpactiveSheet -> setCellValue("B".$index,$value["CLICK"]);
                    $phpactiveSheet -> setCellValue("C".$index,$value["S1"]);
                    $phpactiveSheet -> setCellValue("D".$index,$value["S2"]);     
                    $index++;
                }
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header('Content-Disposition: attachment;filename="key'.time().'.xls"');
                header('Cache-Control: max-age=0');
                $objWriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
                $objWriter->save('php://output');
                exit;
	}
        
        
        /**
         * 销售分析
         */
        public function actionSale(){
            $model= new Sale('search');
            $model->unsetAttributes();
            $criteria=new CDbCriteria;
            $criteria->group = 't.product_name';
            $data=Sale::model()->findAll($criteria);
            $product_list=array();
            foreach ($data as $value) {
                array_push($product_list,$value->product_name);
            }
            if(empty($product_list)){
                array_push($product_list,'');
            }
//            $product_list=array('味达美','黄飞红','六月鲜','禾然有机酱油','遵循自然');
            $time= date("Y-m-d",time());
            $days=6;
            $this->saleMsg($model,$product_list,'',$time,$days);           
        }
        /**
         * 销售分析搜索
         */
        public function actionSaleSearch(){
            //
            $product_name=$_GET['Sale']['product_name'];
            //
            $model= new Sale('search');
            $model->unsetAttributes();
            $model->product_name=$product_name;
            $criteria=new CDbCriteria;
            $criteria->compare('t.product_name',$product_name);
            $criteria->group = 't.product_name';
            $data=Sale::model()->findAll($criteria);
            $product_list=array();
            foreach ($data as $value) {
                array_push($product_list,$value->product_name);
            }
            if(!empty ($_GET['saleTime']) &&!empty ($_GET['saleTime1']) ){
                $time=date("Y-m-d",strtotime($_GET['saleTime']));
                $time_1=date("Y-m-d",strtotime($_GET['saleTime1']));
                $d1=strtotime($time);   
                $d2=strtotime($time_1);   
                $days=round(($d2-$d1)/3600/24); 
                if($days>=0){
                    $this->saleMsg($model,$product_list,$time,$time_1,$days);
                }else{
                    $this->saleMsg($model,$product_list,$time_1,$time,abs($days));
                }
            }  else {
                $time= date("Y-m-d",time());
                $days=6;
                $this->saleMsg($model,$product_list,'',$time,$days);                   
            }

        }
        /**
         * 
         * @param type $product_name
         * @param type $start_time
         * @param type $days
         * @return array
         * 获取某商品的数量列表
         */
        public function getCountTime($product_name,$start_time,$days){
            $count_arr=array();
            if($product_name==''){
                return $count_arr;
            }
            $connection = Yii::app()->db;
            //循环执行sql查询商品数量
            for ($i=0; $i<=$days; $i++) {
                $time=date('Y-m-d', strtotime ("-".$i." day", strtotime($start_time)));
//                $time= date("Y-m-d",strtotime("-".$i." day"));
                $sql="SELECT IFNULL ( SUM(sale_count),0) AS count FROM sale WHERE product_name = '".$product_name."' AND add_time LIKE '".$time."%'";  
                $command = $connection->createCommand($sql);
                $result = $command->queryAll();               
                $count=$result[0]['count']; 
                array_push($count_arr,$count);
            }
            return $count_arr;           
        }
        /**
         * 
         * @param type $product_list
         * @param type $time
         * @param type $days
         * 查询前的数据
         */
        public function saleMsg($model,$product_list,$date,$time,$days){
            $list=array();
            for($i=0;$i<count($product_list);$i++){
                $list_1=$this->getCountTime($product_list[$i],$time,$days);
                array_push($list,$list_1);
            }
            $time_arr=array();
            $date_time='';
            for($i=0;$i<=$days;$i++){
                $times= date("m-d",strtotime("-".$i." day",strtotime($time)));
                array_push($time_arr,$times);
                if ($i==$days){
                    $date_time=date("Y-m-d",strtotime("-".$i." day",strtotime($time)));
                }
            }
//            $model=new Sale;
            $model->integral_qty=$date_time;
            $model->add_time=$time;
            $this->render('sale',array(
                'model'=>$model,
                'data'=>$list,
                'time_arr'=>$time_arr,
                'product_list'=>$product_list,
                'date_1'=>$date,
            ));               
        }  
        
        
        
         public function actionDataExcel(){
                 $model=new Party('search');
		$model->unsetAttributes();  //清空并初始化
                
                $province="";
                $city="";
                $area="";
                if(isset($_GET['Party'])){
                    $province=$_GET['Party']['province'];
                    $city=$_GET['Party']['city'];
                    $area=$_GET['Party']['area'];
                    $model->city=$_GET['Party']['city'];
                    $model->area=$_GET['Party']['area'];
                    $model->province=$_GET['Party']['province'];
                }
                //查询符合条件的地区
                $sql="SELECT PROVINCE,CITY,AREA FROM party WHERE PROVINCE LIKE '%".$province."%' AND CITY LIKE '%".$city."%' AND AREA LIKE '%".$area."%' GROUP BY PROVINCE,CITY,AREA ORDER BY PROVINCE";
                $connection = Yii::app()->db;
                //print_r($sql);
                $command_1 = $connection->createCommand($sql);
                $result_1 = $command_1->queryAll();
                //print_r($result_1);
                $res=array();
                //total系列为表格最后一行统计值
                $total=0;
                $total2=0;
                $total3=0;
                $total4=0;
                $total5=0;
                $total6=0;
                $total7=0;
                $total8=0;
                
                //标志位 记录是否是同一个地区的数据
                $flag_province=0;
                $flag_city="";
                $total_area=0;
                $temp=0;
                for($temp;$temp < count($result_1);$temp++){
                        $p=$result_1[$temp]['PROVINCE'];
                        $c=$result_1[$temp]['CITY'];
                        $a=$result_1[$temp]['AREA'];
                                                
                        $res["$temp"]["1"]=$p;
                        $res["$temp"]["2"]=$c;
                        $res["$temp"]["3"]=$a;
                                                
                        $sql="SELECT * FROM (
                            (SELECT COUNT(*) AS TOTAL FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."') AS TOTAL,
                            (SELECT COUNT(*) AS MAN FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND SEX ='男') AS MAN,
                            (SELECT COUNT(*) AS WOMAN FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND SEX ='女') AS WOMAN,
                            (SELECT COUNT(*) AS AGE1 FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND AGE <20) AS AGE1,
                            (SELECT COUNT(*) AS AGE2 FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND AGE >=20 AND AGE<30 ) AS AGE2,
                            (SELECT COUNT(*) AS AGE3 FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND AGE >=30 AND AGE<40 ) AS AGE3,
                            (SELECT COUNT(*) AS AGE4 FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND AGE >=40 AND AGE<50 ) AS AGE4,
                            (SELECT COUNT(*) AS AGE5 FROM party WHERE PROVINCE = '".$p."' AND CITY = '".$c."' AND AREA = '".$a."' AND AGE >=50) AS AGE5
                         )";
                        
                        $command_2 = $connection->createCommand($sql);
                        $result_2 = $command_2->queryAll();
                        $res["$temp"]["4"]=$result_2[0]['TOTAL'];
                        $res["$temp"]["5"]=round(($result_2[0]['MAN']/$result_2[0]['TOTAL'])*100,0)."%";
                        $res["$temp"]["6"]=round(($result_2[0]['WOMAN']/$result_2[0]['TOTAL'])*100,0)."%";
                        $res["$temp"]["7"]=$result_2[0]['AGE1'];
                        $res["$temp"]["8"]=$result_2[0]['AGE2'];
                        $res["$temp"]["9"]=$result_2[0]['AGE3'];
                        $res["$temp"]["10"]=$result_2[0]['AGE4'];
                        $res["$temp"]["11"]=$result_2[0]['AGE5'];
                        $res["$temp"]["12"]=0;
                        $res["$temp"]["13"]=0;
                        $total+=$result_2[0]['TOTAL'];
                        $total2+=$result_2[0]['MAN'];
                        $total3+=$result_2[0]['WOMAN'];
                        $total4+=$result_2[0]['AGE1'];
                        $total5+=$result_2[0]['AGE2'];
                        $total6+=$result_2[0]['AGE3'];
                        $total7+=$result_2[0]['AGE4'];
                        $total8+=$result_2[0]['AGE5'];
                        
                        //计算区域占比
                        if($flag_city==""){
                            $flag_province=$p;
                            $flag_city=$c;
                            $total_area+=$result_2[0]['TOTAL'];
                        }else{
                            if($flag_province==$p && $flag_city==$c){
                                $total_area+=$result_2[0]['TOTAL'];
                            }else{
                                $index=$temp-1;
                                while($index>=0&&$res["$index"][1]==$flag_province && $res["$index"][2]==$flag_city){
                                    $val=number_format($res["$index"]["4"]/$total_area*100,2);
                                    $res["$index"]["13"]=$val ."%";
                                    $index--;
                                }
                                $flag_province=$p;
                                $flag_city=$c;
                                $total_area=$result_2[0]['TOTAL'];
                            }
                        }
                }
                
                $totals=array('$total1'=>0,'$total2'=>0 ."%",'$total3'=>0 ."%",'$total4'=>0,'$total5'=>0,'$total6'=>0,'$total7'=>0,'$total8'=>0);
                if($total>0){
                    //计算最后一个省市数据的区域占比
                    $index=$temp-1;
                    while($index>=0&&$res["$index"][1]==$flag_province && $res["$index"][2]==$flag_city){
                        $res["$index"]["13"]=$res["$index"]["4"]/$total_area*100 ."%";
                        $index--;
                    }
                    
                    $last="";
                    foreach($res as $key => $value){
                        //计算整体占比
                        $res["$key"]["12"]=round(($res["$key"]["4"]/$total)*100,0)."%";
                        //去除重复省市表头显示
                        if($last==$res["$key"]["1"].$res["$key"]["2"]){
                            $res["$key"]["1"]="";
                            $res["$key"]["2"]="";
                        }else{
                            $last=$res["$key"]["1"].$res["$key"]["2"];
                        }
                    }
                                
                    $totals=array('$total1'=>$total,'$total2'=>round($total2/$total*100,0)."%",'$total3'=>round($total3/$total*100,0)."%",'$total4'=>$total4,'$total5'=>$total5,'$total6'=>$total6,'$total7'=>$total7,'$total8'=>$total8);
                }
                
                
                     include_once(dirname(dirname(__FILE__)).'/extensions/PHPExcel/PHPExcel/IOFactory.php');  
                    $phpexcel = new PHPExcel();
                    $phpexcel->setActiveSheetIndex(0);
                    $phpactiveSheet = $phpexcel->getActiveSheet();
                    $phpactiveSheet -> setTitle("来访儿童年龄分析");
                    $phpactiveSheet -> setCellValue("A1","省");
                    $phpactiveSheet -> setCellValue("B1","市");
                    $phpactiveSheet -> setCellValue("C1","区");
                    $phpactiveSheet -> setCellValue("D1","人数");     
                    $phpactiveSheet -> setCellValue("E1","男所占比例");
                    $phpactiveSheet -> setCellValue("F1","女所占比例");
                    $phpactiveSheet -> setCellValue("G1","20岁以下人数");
                    $phpactiveSheet -> setCellValue("H1","20-30岁人数");
                    $phpactiveSheet -> setCellValue("I1","30-40岁人数");
                    $phpactiveSheet -> setCellValue("J1","40-50岁人数");
                    $phpactiveSheet -> setCellValue("K1","50岁以上人数");
                    $phpactiveSheet -> setCellValue("L1","整体占比");
                    $phpactiveSheet -> setCellValue("M1","区域占比");
                    $index=2;
                    foreach ($res as $value){
                        $phpactiveSheet -> setCellValue("A".$index,$value["1"]);
                        $phpactiveSheet -> setCellValue("B".$index,$value["2"]);
                        $phpactiveSheet -> setCellValue("C".$index,$value["3"]);
                        $phpactiveSheet -> setCellValue("D".$index,$value["4"]);     
                        $phpactiveSheet -> setCellValue("E".$index,$value["5"]);
                        $phpactiveSheet -> setCellValue("F".$index,$value["6"]);
                        $phpactiveSheet -> setCellValue("G".$index,$value["7"]);
                        $phpactiveSheet -> setCellValue("H".$index,$value["8"]);
                        $phpactiveSheet -> setCellValue("I".$index,$value["9"]);
                        $phpactiveSheet -> setCellValue("J".$index,$value["10"]);
                        $phpactiveSheet -> setCellValue("K".$index,$value["11"]);
                        $phpactiveSheet -> setCellValue("L".$index,$value["12"]);
                        $phpactiveSheet -> setCellValue("M".$index,$value["13"]);
                        $index++;
                    }
                    $phpactiveSheet -> setCellValue("A".$index,'合计');
                    $phpactiveSheet -> setCellValue("D".$index,$totals['$total1']);     
                    $phpactiveSheet -> setCellValue("E".$index,$totals['$total2']);
                    $phpactiveSheet -> setCellValue("F".$index,$totals['$total3']);
                    $phpactiveSheet -> setCellValue("G".$index,$totals['$total4']);
                    $phpactiveSheet -> setCellValue("H".$index,$totals['$total5']); 
                    $phpactiveSheet -> setCellValue("I".$index,$totals['$total6']); 
                    $phpactiveSheet -> setCellValue("J".$index,$totals['$total7']); 
                    $phpactiveSheet -> setCellValue("K".$index,$totals['$total8']); 

                    
                    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                    header('Content-Disposition: attachment;filename="renqun'.time().'.xls"');
                    header('Cache-Control: max-age=0');
                    $objWriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
                    $objWriter->save('php://output');
                    exit;

               
    }
        
        
        
	/**
	 * Returns the data model based on the primary key given in the GET variable.
	 * If the data model is not found, an HTTP exception will be raised.
	 * @param integer $id the ID of the model to be loaded
	 * @return Sms the loaded model
	 * @throws CHttpException
	 */
	public function loadModel($id)
	{
		$model=Sms::model()->findByPk($id);
		if($model===null)
			throw new CHttpException(404,'The requested page does not exist.');
		return $model;
	}

	/**
	 * Performs the AJAX validation.
	 * @param Sms $model the model to be validated
	 */
	protected function performAjaxValidation($model)
	{
		if(isset($_POST['ajax']) && $_POST['ajax']==='sms-form')
		{
			echo CActiveForm::validate($model);
			Yii::app()->end();
		}
	}
	
}
